/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package xml.dao;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.SQLException;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.List;
import xml.entity.Account;
import xml.entity.Jobseeker;
import xml.entity.Jobseekers;
import xml.util.DBConnection;

/**
 *
 * @author Duc Huy
 */
public class JobseekerDAO {
    DBConnection conInfo;

    public int getNextJobseekerId() throws SQLException, Exception {
        Connection conn = null;
        Statement stm = null;
        conInfo = new DBConnection();
        ResultSet rs = null;
        try {
            Class.forName(conInfo.getDriverName());
            conn = DriverManager.getConnection(conInfo.getURL(), conInfo.getUsername(), conInfo.getPassword());
            String strQuery = "SELECT jobSeekerId FROM jobseeker";

            stm = conn.createStatement();
            rs = stm.executeQuery(strQuery);

            int result = 0;
            int i = 0;
            while (rs.next()) {
                result = Integer.parseInt(rs.getString("jobSeekerId"));
                i = i++;
            }

            stm.close();
            conn.close();

            return result+1;

        } catch (SQLException e) {
            throw (e);
        } catch (Exception e) {
            throw (e);
        } finally {
            try {
                stm.close();
            } catch (SQLException e) {
                throw (e);
            }
            try {
                conn.close();
            } catch (SQLException e) {
                throw (e);
            }
        }
    }

    public void insertJobseeker(Jobseeker jobseeker) throws SQLException, Exception {
        Connection conn = null;
        Statement stm = null;
        conInfo = new DBConnection();

        try {
            Class.forName(conInfo.getDriverName());
            conn = DriverManager.getConnection(conInfo.getURL(), conInfo.getUsername(), conInfo.getPassword());
            StringBuilder strBuilder = new StringBuilder();

            strBuilder.append("INSERT INTO Jobseeker (jobSeekerId, account_username, fullname,"
                    + "dayOfBirth, monthOfBirth, yearOfBirth, gender, marriageStatus, address, city, phone)"
                    + " VALUES ('").append(jobseeker.getJobSeekerId()).
                    append("', '").append(jobseeker.getAccountUsername()).
                    append("', '").append(jobseeker.getFullname()).
                    append("', '").append(jobseeker.getDayOfBirth()).
                    append("', '").append(jobseeker.getMonthOfBirth()).
                    append("', '").append(jobseeker.getYearOfBirth()).
                    append("', '").append(jobseeker.getGender()).
                    append("', '").append(jobseeker.getMarriageStatus()).
                    append("', '").append(jobseeker.getAddress()).
                    append("', '").append(jobseeker.getCity()).
                    append("', '").append(jobseeker.getPhone()).append("')");
                    stm = conn.createStatement();
            stm.executeUpdate(strBuilder.toString());

            stm.close();
            conn.close();
        } catch (SQLException e) {
            throw (e);
        } catch (Exception e) {
            throw (e);
        } finally {
            try {
                stm.close();
            } catch (SQLException e) {
                throw (e);
            }
            try {
                conn.close();
            } catch (SQLException e) {
                throw (e);
            }
        }
    }

    public void updateJobseeker(Jobseeker jobseeker, String username) throws SQLException, Exception {
        Connection conn = null;
        Statement stm = null;
        conInfo = new DBConnection();

        try {
            Class.forName(conInfo.getDriverName());
            conn = DriverManager.getConnection(conInfo.getURL(), conInfo.getUsername(), conInfo.getPassword());
            StringBuilder strBuilder = new StringBuilder();

            strBuilder.append("UPDATE Jobseeker SET fullname = '").append(jobseeker.getFullname()).append("', ");
            strBuilder.append("dayOfBirth = '").append(jobseeker.getDayOfBirth()).append("', ");
            strBuilder.append("monthOfBirth = '").append(jobseeker.getMonthOfBirth()).append("', ");
            strBuilder.append("yearOfBirth = '").append(jobseeker.getYearOfBirth()).append("', ");
            strBuilder.append("gender = '").append(jobseeker.getGender()).append("', ");
            strBuilder.append("marriageStatus = '").append(jobseeker.getMarriageStatus()).append("', ");
            strBuilder.append("address = '").append(jobseeker.getAddress()).append("', ");
            strBuilder.append("city = '").append(jobseeker.getCity()).append("', ");
            strBuilder.append("phone = '").append(jobseeker.getPhone()).append("' ");
            strBuilder.append(" WHERE account_username = '").append(username).append("'");

  //          System.out.println("update js query:" + strBuilder);
            stm = conn.createStatement();
            stm.executeUpdate(strBuilder.toString());

            stm.close();
            conn.close();
        } catch (SQLException e) {
            throw (e);
        } catch (Exception e) {
            throw (e);
        } finally {
            try {
                stm.close();
            } catch (SQLException e) {
                throw (e);
            }
            try {
                conn.close();
            } catch (SQLException e) {
                throw (e);
            }
        }
    }

    public Jobseeker getCurrentJobSeeker(String username) throws SQLException, Exception {
        Jobseeker js = new Jobseeker();
     //   Jobseekers jsList = new Jobseekers();
        Connection conn = null;
        Statement stm = null;
        ResultSet rs = null;
        conInfo = new DBConnection();

        try {
            Class.forName(conInfo.getDriverName());
            conn = DriverManager.getConnection(conInfo.getURL(), conInfo.getUsername(), conInfo.getPassword());
            StringBuilder strBuilder = new StringBuilder();
            strBuilder.append("SELECT * FROM Jobseeker");
            strBuilder.append(" WHERE account_username = '").append(username).append("'");
            stm = conn.createStatement();
            rs = stm.executeQuery(strBuilder.toString());
            while (rs.next()) {
                js.setJobSeekerId(rs.getInt("jobSeekerId"));
                js.setFullname(rs.getString("fullname"));
                js.setDayOfBirth(rs.getInt("dayOfBirth"));
                js.setMonthOfBirth(rs.getInt("monthOfBirth"));
                js.setYearOfBirth(rs.getInt("yearOfBirth"));
                js.setGender(rs.getShort("gender"));
                js.setMarriageStatus(rs.getShort("marriageStatus"));
                js.setPicture("");
                js.setAddress(rs.getString("address"));
                js.setCity(rs.getString("city"));
                js.setPhone(rs.getString("phone"));
            }
            return js ;

        } catch (SQLException e) {
            throw (e);
        } catch (Exception e) {
            throw (e);
        } finally {
            try {
                stm.close();
            } catch (SQLException e) {
                throw (e);
            }
            try {
                conn.close();
            } catch (SQLException e) {
                throw (e);
            }
        }
    }
}